-- @owner: @zhangbingliang
-- @date: 2025-07-31
-- @testpoint: fetch的不同操作后使用@@FETCH_STATUS查看状态
-- @modify: modified by @zhangbingliang at 2025-07-31

--prestep1:连接到D库
@conn PrimaryDbDmode;

--step1:建表;expect:成功
drop table if exists t_fetch0003;
CREATE TABLE t_fetch0003 (
 ID INT PRIMARY KEY,
 Value NVARCHAR(50)
);

INSERT INTO t_fetch0003 VALUES (1, 'A'), (2, 'B'), (3, 'C');

--定义游标，并使用FETCH NEXT遍历结果集;expect:成功，fecth_status前三次返回0，最后一次返回-1
begin; /
DECLARE cur_fetch0003 CURSOR FOR SELECT ID, Value FROM t_fetch0003; /
FETCH NEXT FROM cur_fetch0003;
select @@FETCH_STATUS;

FETCH LAST FROM cur_fetch0003;
select @@FETCH_STATUS;

FETCH PRIOR FROM cur_fetch0003;
select @@FETCH_STATUS; 

FETCH first FROM cur_fetch0003;
select @@FETCH_STATUS; 

FETCH absolute 1 FROM cur_fetch0003;
select @@FETCH_STATUS;

FETCH relative 1 FROM cur_fetch0003;
select @@FETCH_STATUS; 

FETCH 1 FROM cur_fetch0003;
select @@FETCH_STATUS;

FETCH forward FROM cur_fetch0003;
select @@FETCH_STATUS; 

FETCH forward 2 FROM cur_fetch0003;
select @@FETCH_STATUS; 

FETCH forward all FROM cur_fetch0003;
select @@FETCH_STATUS; 

FETCH backward FROM cur_fetch0003;
select @@FETCH_STATUS; 

FETCH backward 2 FROM cur_fetch0003;
select @@FETCH_STATUS; 

FETCH backward all FROM cur_fetch0003;
select @@FETCH_STATUS; 
CLOSE cur_fetch0003;
end;

--清理环境;expect:成功
drop table if exists t_fetch0003;